<?php

namespace app\common\func;

//PRO1.0数据查询
use app\common\model\AllocationDetails;
use app\common\model\EquipRate;
use app\common\model\FlowInfo;
use app\common\model\Policy;
use app\common\model\PolicyAdjust;
use app\common\model\PolicyProfit;
use app\common\model\PolicySimcard;
use app\common\model\PolicyStandard;
use app\common\model\PolicyStandards;
use app\common\model\Product;
use app\common\model\StandardRecord;
use app\common\model\User;
use app\common\model\YsInfo;
use think\Db;

class Libs
{

    public static function getYsInfo($merchantId){
        return YsInfo::where(['merchant_id' => $merchantId])->find();
    }

    public static function getStandardRecord($sn){
        return StandardRecord::where(["sn" => $sn,"status" => 0])->find();
    }

    public static function saveBillLog($day,$remark){
        $data = ["day" => $day,"remark" => $remark];
        Db::name("bill_error_log")->insert($data);
        exit;
    }

    public static function getStandardUnfinished($sn){
        return StandardRecord::where(['sn' => $sn,'status' => 0])->find();
    }

    public static function getFlowHowMany($sn){
        return FlowInfo::where(['sn' => $sn])->count();
    }

    public static function getStandardDetails(array $productId,$startTime,$userIds,$status,$day,$search){
        $query = Db::name("standard_record")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["e.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($status > 0){
            $query->where(["f.status" => $status]);
        }else{
            $query->where(["f.status" => ['in',[1,2]]]);
        }

        if($search != ""){
            $query->where(["e.sn" => ["like","%".$search."%"]]);
        }

        $query->where("FROM_UNIXTIME(f.trading_time, '%Y-%m-%d') = '" . $day."'");

        return $query->field("e.*,f.end_day,f.trading_time,f.require_money,f.reward,f.accumulate_money,f.total_money,f.status as standard_status")
            ->group("f.trading_time desc")
            ->select();
    }

    public static function getStandardCount(array $productId,$startTime,$userIds,$status){
        $query = Db::name("standard_record")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["e.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($status > 0){
            $query->where(["f.status" => $status]);
        }else{
            $query->where(["f.status" => ['in',[1,2]]]);
        }

        return $query->count();
    }

    public static function getStandardData(array $productId,$startTime,$userIds,$status,$month = ""){
        $query = Db::name("standard_record")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["e.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($status > 0){
            $query->where(["f.status" => $status]);
        }else{
            $query->where(["f.status" => ['in',[1,2]]]);
        }

        if($month != ""){
            $format = '%Y-%m-%d';
            $query->where("FROM_UNIXTIME(f.trading_time, '%Y-%m') = '" . $month."'");
        }else{
            $format = "%Y-%m";
        }

        return $query->field("FROM_UNIXTIME(f.trading_time, '".$format."') as date,COUNT(f.id) as total")
            ->group("date desc")
            ->select();
    }

    public static function getFlowDetails(array $productId,$startTime,$userIds,$howMany,$day,$search){
        $query = Db::name("flow_info")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["f.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["f.status" => 1])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($search != ""){
            $query->where(["e.sn" => ["like","%".$search."%"]]);
        }

        if($howMany > 0){
            $query->where(["f.how_many" => ["in",$howMany]]);
        }

        $query->where("FROM_UNIXTIME(f.trading_time, '%Y-%m-%d') = '" . $day."'");

        return $query->field("e.*,f.order_num,f.trading_time,f.flow_money,f.how_many")
            ->order("f.trading_time desc")
            ->select();
    }

    public static function getFlowTimes($startTime,$userIds){
        $query = Db::name("flow_info")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["f.trading_time" => [">=",$startTime]])
            ->where(["e.main_id" => ["in",$userIds]]);

        return $query->group("how_many")->column("how_many");
    }

    public static function getAllProductIds(){
        return Product::where("status","=",1)->column("id");
    }

    public static function getFlowCount(array $productId,$startTime,$userIds,$howMany){
        $query = Db::name("flow_info")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["f.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["f.status" => 1])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($howMany > 0){
            $query->where(["f.how_many" => ["in",$howMany]]);
        }

        return $query->count();
    }

    public static function getFlowData(array $productId,$startTime,$userIds,$howMany,$month = ""){
        $query = Db::name("flow_info")->alias("f")
            ->join("equip e","f.sn = e.sn","left")
            ->where(["f.product_id" => ["in",$productId],"f.trading_time" => [">=",$startTime]])
            ->where(["f.status" => 1])
            ->where(["e.main_id" => ["in",$userIds]]);

        if($howMany > 0){
            $query->where(["f.how_many" => ["in",$howMany]]);
        }

        if($month != ""){
            $format = '%Y-%m-%d';
            $query->where("FROM_UNIXTIME(f.trading_time, '%Y-%m') = '" . $month."'");
        }else{
            $format = "%Y-%m";
        }

        return $query->field("FROM_UNIXTIME(f.trading_time, '".$format."') as date,COUNT(f.id) as total")
            ->group("date desc")
            ->select();
    }

    public static function getUserLevel($userId){
        return User::where(['id' => $userId])->value("buy_level");
    }

    /**
     * @param $sn
     * @param $type "D"默认 "T"调价
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getEquipRate($sn,$type){
        $query = EquipRate::where(['sn' => $sn,'state' => $type]);

        if($type == "D"){
            return $query->find();
        }elseif ($type == "T"){
            return $query->order("rate asc")->select();
        }else{
            return null;
        }

    }

    public static function getProfit($policyId,$productId,$rate){
        $rate = bcmul($rate,1,2);
        return PolicyProfit::where(['policy_id' => $policyId,'product_id' => $productId,'profit_rate' => $rate])->find();
    }

    public static function getOrderProductSnList($orderProductId){
        return Db::name("allocation_details")->alias("a")
            ->join("order_product b", "b.allocation_id = a.allocation_id", 'LEFT')
            ->where(['b.id' => $orderProductId])
            ->column("a.sn");
    }

    //查询产品达标对应角色的天数限制
    public static function getStandardsDay($productId,$role){
        return PolicyStandards::where(['product_id' => $productId,'role' => $role])->find();
    }

    public static function getPolicyFlow($productId){
        return PolicySimcard::where(['policy_id' => $productId])->find();
    }

    public static function getPolicyFlowByProduct($productId){
        return PolicySimcard::where(['product_id' => $productId])->find();
    }

    public static function getPolicyByProductsLimit($productId,$requireMoney){
        return PolicyStandards::where(['product_id' => $productId,"money" => $requireMoney])->select();
    }

    public static function getPolicyByProduct($productId){
        return Policy::where(["product_id" => $productId])->find();
    }

    public static function getPolicyProfitList($policyId){
        return PolicyProfit::where(["policy_id" => $policyId])->select();
    }

    public static function getPolicyAdjust($policyId){
        return PolicyAdjust::where(['policy_id' => $policyId])->find();
    }

    public static function getPolicyStandard($policyId)
    {
        return PolicyStandard::where(['policy_id' => $policyId])->select();
    }

    public static function getPolicyStandards($policyId)
    {
        return PolicyStandards::where(['policy_id' => $policyId])->select();
    }

    public static function getPolicyStandardsByProduct($productId)
    {
        return PolicyStandards::where(['product_id' => $productId])->select();
    }

    public static function getUnStandardEquip()
    {
        return StandardRecord::where(["status" => 0,"step" => 4])->where(" total_money >= require_money")->select();
    }

    public static function getUnFlowEquip()
    {
        $timeStamp = strtotime(date("Y-m-d"));
        return FlowInfo::where(["status" => "0"])->where(["trading_time" => ["<",$timeStamp]])->select();
    }

}
